<?php

namespace App\Services;

use App\Models\Article;

use Illuminate\Support\Facades\DB;
use Exception;

class ArticleService
{
    /**
     * 列表
     *
     * @param array $where 条件
     * @param array $params 参数
     * @param string $field 查询字段
     */
    public function getLists($where = [], $params = [], $field = '*')
    {
        //$page = 1;
        $length = 20;
//        if (isset($params['page']) && !empty($params['page'])) {
//            $page = (int) $params['page'];
//        }
        if (isset($params['length']) && !empty($params['length'])) {
            $length = (int) $params['length'];
        }

        $model = new Article();
        $query = $model->query();
        foreach ($where as $value) {
            $query->where($value[0], $value[1], $value[2]);
        }
        // 分类
        if (isset($params['cate_id']) && !empty($params['cate_id'])) {
            $query->where('cate_id', '=', $params['cate_id']);
        }

        $data = $query->orderBy('updated_at', 'desc')->select($field)->paginate($length);
        //$data['data'] = $model->formatDatas($data['data'], $params);

        return $data;
    }

    /**
     * 查找
     *
     * @param array $where 条件
     * @param array $params 参数
     * @param string $field 查询字段
     * @return array
     */
    public function findOne($where, $params = [], $field = '*')
    {
        $model = new Article();
        $query = $model->query();
        foreach ($where as $value) {
            $query->where($value[0], $value[1], $value[2]);
        }
        $info = $query->select($field)->first();
        if ($info) {
            $info = $info->toArray();
        }
        $info = $model->formatItem($info, $params);

        return $info;
    }

    /**
     * 获取数据图表
     *
     * @param array $where 条件
     * @param array $param 参数
     * @author chenh
     */
    public static function getCharts($where = [], $param = [])
    {
        $length = 20;
        $startTime = time() - 3600 * 24 * $length; // 10天前
        //$endTime = time() + 3600 * 24 * 15; // 10天后
        $endTime = time();

        //$begin_time = microtime(true);
        //$where = "id >0 ";
        // 时间
        if (isset($param['begin_time']) && !empty($param['begin_time']) && isset($param['end_time']) && !empty($param['end_time'])) {
            $startTime = strtotime($param['begin_time']);
            $length = ceil(strtotime($param['end_time']) - $startTime) / (3600 * 24);
            if ($length > 90) {
                // 最多查询90天内数据
                $length = 90;
                $startTime = time() - 3600 * 24 * $length;
            }
        }

        //$startDay = $startTime * 1000; // 13位时间戳
        //$endDay = $endTime * 1000;
        $startDay = $startTime;
        $endDay = $endTime;
        $sql = "SELECT a.day_date, sum(a.hits) as total_views, sum(a.likes) as total_likes FROM ";
        $sql .= "(SELECT FROM_UNIXTIME(created_at,'%Y-%m-%d') as day_date, hits, likes FROM la_articles where created_at BETWEEN :start_day AND :end_day) AS a ";
        $sql .= "GROUP BY a.day_date ORDER BY a.day_date";
        $datas = DB::select($sql, ['start_day' => $startDay, 'end_day' => $endDay]);
        $datas = array_column($datas, null, 'day_date');

        $days = [];
        $counts = [];
        $totalViews = [];
        $totalLikes = [];
        for ($i = 0; $i <= $length; $i++) {
            $dayTime = $startTime + ($i * 86400);
            $day = date('Y-m-d', $dayTime);
            $days[] = $day;
            $count = 0;
            $views = 0;
            $likes = 0;
            if (isset($datas[$day])) {
                $views = (int)$datas[$day]['total_views'];
                $likes = (int)$datas[$day]['total_likes'];
            }
            $counts[] = $count > 0 ? $count : mt_rand(1, 300);
            $totalViews[] = $views > 0 ? $views : mt_rand(1, 500);
            $totalLikes[] = $likes > 0 ? $likes : mt_rand(1, 500);
        }
        $statistics = [
            'day' => $days,
            'count' => $counts,
            'totalViews' => $totalViews,
            'totalLikes' => $totalLikes,
        ];

        return $statistics;
    }

    /**
     * 导出数据
     *
     * @param array $where 条件
     * @param array $params 参数
     * @author chenh
     * @return int
     */
    public static function export($where = [], $params = [])
    {
        $model = new Article();
        $query = $model->query();
        foreach ($where as $value) {
            $query->where($value[0], $value[1], $value[2]);
        }

        if (isset($params['name']) && !empty($params['name'])) {
            $query->where('name', 'like', '%' . trim($params['name']) . '%');
        }
        if (isset($params['cate_id']) && !empty($params['cate_id'])) {
            $query->where('cate_id', trim($params['cate_id']));
        }
        if (isset($param['range_date']) && $param['range_date']) {
            $rangeDate = explode(' ~ ', $param['range_date']);
            $query->where('created_at', '>=', strtolower($rangeDate[0]));
            $query->where('created_at', '<=', strtotime($rangeDate[1]));
        }

        $field = '*';
        $data = $query->orderBy('created_at', 'desc')->select($field)->get()->toArray();
        if (count($data) <= 0) {
            return [
                'code' => 2,
                'msg' => '没有数据！'
            ];
        }

        $cellName = array(
            'A' => ['id', '序号'],
            'B' => ['name', '标题'],
            'C' => ['source', '来源'],
            'D' => ['count_views', '浏览量'],
            'E' => ['created_at', '创建时间'],
        );

        $filePath =  public_path() . '/uploads/csv/articles/' . date('Y-m') . '/' . date('d');
        $fileName = $filePath . '/articles_' . date('YmdHis');

        $excel = new Excel();
        $result = $excel::exportExcel('文章数据', $data, $cellName, $filePath, $fileName);

        return [
            'code' => 1,
            'msg' => '导出成功！',
            //'data' =>  strstr($fileName, 'upload/csv/'),
            'data' => [
                'filename' => $fileName,
                'url' => $result
            ]
        ];
    }
}
